<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL时间类型详解：DATETIME vs TIMESTAMP</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css">
    <link rel="stylesheet" href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            color: #333;
            line-height: 1.6;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #6e8efb 0%, #a777e3 100%);
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 20px 25px -5px rgba(0, 0, 0, 0.1), 0 10px 10px -5px rgba(0, 0, 0, 0.04);
        }
        .code-block {
            background-color: #2d3748;
            border-radius: 0.5rem;
            overflow-x: auto;
        }
        .feature-icon {
            width: 48px;
            height: 48px;
            display: flex;
            align-items: center;
            justify-content: center;
            border-radius: 12px;
            margin-right: 1rem;
        }
        ::selection {
            background-color: rgba(167, 119, 227, 0.3);
        }
        .mermaid {
            background-color: white;
            border-radius: 0.5rem;
            padding: 1.5rem;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1), 0 2px 4px -1px rgba(0, 0, 0, 0.06);
        }
    </style>
</head>
<body class="bg-gray-50">
    <!-- Hero Section -->
    <section class="hero-gradient text-white py-20">
        <div class="container mx-auto px-6 max-w-4xl">
            <div class="flex flex-col items-center text-center">
                <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL时间类型详解</h1>
                <p class="text-xl md:text-2xl font-light mb-8 opacity-90">深入理解DATETIME与TIMESTAMP的核心区别与应用场景</p>
                <div class="w-20 h-1 bg-white opacity-50 mb-8"></div>
                <p class="text-lg md:text-xl max-w-2xl leading-relaxed">
                    在数据库设计中，时间类型的选择往往被忽视，但它对应用的正确性和性能有着深远影响。本文将详细解析MySQL中DATETIME与TIMESTAMP的差异，助您做出明智选择。
                </p>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <main class="container mx-auto px-6 py-16 max-w-4xl">
        <!-- Intro Section -->
        <section class="mb-16">
            <p class="text-gray-700 mb-8 text-lg leading-relaxed">
                在MySQL中，<code class="bg-gray-100 px-2 py-1 rounded text-indigo-600">DATETIME</code>和<code class="bg-gray-100 px-2 py-1 rounded text-indigo-600">TIMESTAMP</code>是两种常用的日期和时间数据类型，它们用于存储时间信息，但在一些方面存在显著的区别。选择哪种数据类型取决于你的应用场景和需求。
            </p>
            
            <div class="mermaid mb-12">
                flowchart TD
                    A[DATETIME vs TIMESTAMP] --> B[存储范围]
                    A --> C[时区处理]
                    A --> D[存储空间]
                    A --> E[自动更新]
                    B --> F[DATETIME: 1000-9999年]
                    B --> G[TIMESTAMP: 1970-2038年]
                    C --> H[DATETIME: 固定]
                    C --> I[TIMESTAMP: 自动转换]
                    D --> J[DATETIME: 8字节]
                    D --> K[TIMESTAMP: 4字节]
                    E --> L[DATETIME: 不支持]
                    E --> M[TIMESTAMP: 支持]
            </div>
        </section>

        <!-- DATETIME Section -->
        <section class="mb-16">
            <div class="flex items-center mb-8">
                <div class="feature-icon bg-indigo-100 text-indigo-600">
                    <i class="fas fa-calendar-alt text-2xl"></i>
                </div>
                <h2 class="text-3xl font-bold">1. DATETIME类型</h2>
            </div>
            
            <p class="text-gray-700 mb-6">
                <span class="font-semibold text-indigo-600">功能：</span>用于存储日期和时间的组合，精确到秒。
            </p>
            
            <div class="grid md:grid-cols-2 gap-6 mb-8">
                <div class="bg-white p-6 rounded-xl shadow-md card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-indigo-600">主要特点</h3>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <i class="fas fa-calendar mr-3 mt-1 text-indigo-500"></i>
                            <span><span class="font-medium">范围：</span>从<code class="bg-gray-100 px-1">1000-01-01 00:00:00</code>到<code class="bg-gray-100 px-1">9999-12-31 23:59:59</code></span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-globe mr-3 mt-1 text-indigo-500"></i>
                            <span><span class="font-medium">时区：</span>不受时区影响，存储的时间是固定的</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-database mr-3 mt-1 text-indigo-500"></i>
                            <span><span class="font-medium">存储：</span>占用8字节的存储空间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-clock mr-3 mt-1 text-indigo-500"></i>
                            <span><span class="font-medium">默认值：</span>默认为NULL，支持设置固定默认值</span>
                        </li>
                    </ul>
                </div>
                
                <div class="bg-white p-6 rounded-xl shadow-md card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-indigo-600">应用示例</h3>
                    <p class="mb-4">适用于需要存储长期或历史时间数据的场景：</p>
                    <ul class="space-y-2 text-gray-700">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>用户注册时间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>订单创建时间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>历史事件记录</span>
                        </li>
                    </ul>
                    <div class="mt-4 code-block">
                        <pre class="text-gray-200 p-4"><code>CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_datetime DATETIME
);</code></pre>
                    </div>
                </div>
            </div>
        </section>

        <!-- TIMESTAMP Section -->
        <section class="mb-16">
            <div class="flex items-center mb-8">
                <div class="feature-icon bg-purple-100 text-purple-600">
                    <i class="fas fa-clock text-2xl"></i>
                </div>
                <h2 class="text-3xl font-bold">2. TIMESTAMP类型</h2>
            </div>
            
            <p class="text-gray-700 mb-6">
                <span class="font-semibold text-purple-600">功能：</span>用于存储日期和时间的组合，精确到秒。
            </p>
            
            <div class="grid md:grid-cols-2 gap-6 mb-8">
                <div class="bg-white p-6 rounded-xl shadow-md card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-purple-600">主要特点</h3>
                    <ul class="space-y-3">
                        <li class="flex items-start">
                            <i class="fas fa-calendar mr-3 mt-1 text-purple-500"></i>
                            <span><span class="font-medium">范围：</span>从<code class="bg-gray-100 px-1">1970-01-01 00:00:01</code>到<code class="bg-gray-100 px-1">2038-01-19 03:14:07</code></span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-globe mr-3 mt-1 text-purple-500"></i>
                            <span><span class="font-medium">时区：</span>自动转换为UTC存储，查询时转换为当前时区</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-database mr-3 mt-1 text-purple-500"></i>
                            <span><span class="font-medium">存储：</span>占用4字节的存储空间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-clock mr-3 mt-1 text-purple-500"></i>
                            <span><span class="font-medium">默认值：</span>支持<code class="bg-gray-100 px-1">CURRENT_TIMESTAMP</code>和自动更新</span>
                        </li>
                    </ul>
                </div>
                
                <div class="bg-white p-6 rounded-xl shadow-md card-hover transition-all duration-300">
                    <h3 class="text-xl font-semibold mb-4 text-purple-600">应用示例</h3>
                    <p class="mb-4">适用于需要记录时间戳或自动更新时间的场景：</p>
                    <ul class="space-y-2 text-gray-700">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>操作日志时间戳</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>数据最后修改时间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-2 mt-1 text-green-500"></i>
                            <span>需要自动更新的时间字段</span>
                        </li>
                    </ul>
                    <div class="mt-4 code-block">
                        <pre class="text-gray-200 p-4"><code>CREATE TABLE logs (
    log_id INT PRIMARY KEY,
    log_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
                 ON UPDATE CURRENT_TIMESTAMP
);</code></pre>
                    </div>
                </div>
            </div>
        </section>

        <!-- Comparison Section -->
        <section class="mb-16">
            <div class="flex items-center mb-8">
                <div class="feature-icon bg-blue-100 text-blue-600">
                    <i class="fas fa-balance-scale text-2xl"></i>
                </div>
                <h2 class="text-3xl font-bold">3. 核心区别对比</h2>
            </div>
            
            <div class="bg-white rounded-xl shadow-md overflow-hidden mb-8">
                <table class="min-w-full divide-y divide-gray-200">
                    <thead class="bg-gray-50">
                        <tr>
                            <th class="px-6 py-3 text-left text-xs font-medium text-gray-500 uppercase tracking-wider">对比项</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-indigo-500 uppercase tracking-wider">DATETIME</th>
                            <th class="px-6 py-3 text-left text-xs font-medium text-purple-500 uppercase tracking-wider">TIMESTAMP</th>
                        </tr>
                    </thead>
                    <tbody class="bg-white divide-y divide-gray-200">
                        <tr>
                            <td class="px-6 py-4 whitespace-nowrap font-medium text-gray-700">时间范围</td>
                            <td class="px-6 py-4 whitespace-nowrap">1000-01-01 到 9999-12-31</td>
                            <td class="px-6 py-4 whitespace-nowrap">1970-01-01 到 2038-01-19</td>
                        </tr>
                        <tr>
                            <td class="px-6 py-4 whitespace-nowrap font-medium text-gray-700">时区处理</td>
                            <td class="px-6 py-4 whitespace-nowrap">固定不变</td>
                            <td class="px-6 py-4 whitespace-nowrap">自动转换</td>
                        </tr>
                        <tr>
                            <td class="px-6 py-4 whitespace-nowrap font-medium text-gray-700">存储空间</td>
                            <td class="px-6 py-4 whitespace-nowrap">8字节</td>
                            <td class="px-6 py-4 whitespace-nowrap">4字节</td>
                        </tr>
                        <tr>
                            <td class="px-6 py-4 whitespace-nowrap font-medium text-gray-700">自动更新</td>
                            <td class="px-6 py-4 whitespace-nowrap">不支持</td>
                            <td class="px-6 py-4 whitespace-nowrap">支持</td>
                        </tr>
                    </tbody>
                </table>
            </div>
            
            <div class="grid md:grid-cols-2 gap-6">
                <div class="bg-indigo-50 p-6 rounded-xl border-l-4 border-indigo-500">
                    <h3 class="text-xl font-semibold mb-4 text-indigo-700">推荐使用DATETIME的场景</h3>
                    <ul class="space-y-3 text-gray-700">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-indigo-500"></i>
                            <span>需要存储历史日期（如生日、历史事件）</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-indigo-500"></i>
                            <span>需要存储未来日期（如预约时间）</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-indigo-500"></i>
                            <span>需要固定时间记录，不受时区影响</span>
                        </li>
                    </ul>
                </div>
                
                <div class="bg-purple-50 p-6 rounded-xl border-l-4 border-purple-500">
                    <h3 class="text-xl font-semibold mb-4 text-purple-700">推荐使用TIMESTAMP的场景</h3>
                    <ul class="space-y-3 text-gray-700">
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-purple-500"></i>
                            <span>需要记录操作时间戳</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-purple-500"></i>
                            <span>需要自动更新的最后修改时间</span>
                        </li>
                        <li class="flex items-start">
                            <i class="fas fa-check-circle mr-3 mt-1 text-purple-500"></i>
                            <span>需要节省存储空间的时间字段</span>
                        </li>
                    </ul>
                </div>
            </div>
        </section>

        <!-- Summary Section -->
        <section class="bg-gradient-to-r from-blue-500 to-purple-600 rounded-xl p-8 text-white">
            <h2 class="text-2xl font-bold mb-4">决策流程图</h2>
            <div class="mermaid">
                flowchart TD
                    A[需要存储时间数据?] -->|是| B[需要存储1970年以前或2038年以后的日期?]
                    B -->|是| C[使用DATETIME]
                    B -->|否| D[需要自动更新时间或时区转换?]
                    D -->|是| E[使用TIMESTAMP]
                    D -->|否| F[需要节省存储空间?]
                    F -->|是| E
                    F -->|否| C
            </div>
        </section>
    </main>

    <!-- Footer -->
    <footer class="bg-gray-900 text-white py-8">
        <div class="container mx-auto px-6">
            <div class="flex flex-col items-center">
                <div class="text-xl font-semibold mb-2">技术小馆</div>
                <a href="http://www.yuque.com/jtostring" class="text-gray-400 hover:text-white transition-colors duration-300">http://www.yuque.com/jtostring</a>
            </div>
        </div>
    </footer>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>